﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Inventory
{
    public class ReceiptManager:ConnectionManager
    {
        public SqlException AddReceipt(ReceiptUnit oUnit)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO OIGN (DocNum, Reference, [Trans], Remarks, WhsCode, WhsName, PostingDate, DocStatus, DateCreated, CreatedBy, DateModified, ModifiedBy) " +
                    "VALUES (@DocNum, @Reference, @Trans, @Remarks, @WhsCode, @WhsName, @PostingDate, @DocStatus, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy)";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocNumber));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@Trans", oUnit.Transaction));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@WhsCode", oUnit.WarehouseCode));
                oCommand.Parameters.Add(new SqlParameter("@WhsName", oUnit.WarehouseName));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));

                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));
                oCommand.ExecuteNonQuery();

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;

                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "INSERT INTO IGN1 (DocNum, ItemCode, ItemName, Qty, UnitCost, UoM, LotNumber, MfrDate, ExpiryDate) VALUES (@DocNum, @ItemCode, @ItemName, @Qty, @UnitCost, @UoM, @LotNumber, @MfrDate, @ExpiryDate)";
                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocNumber));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemName", oRow["ItemName"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Qty", oRow["Qty"]));
                    oCommand.Parameters.Add(new SqlParameter("@UnitCost", oRow["UnitCost"]));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", oRow["ExpiryDate"]));
                    oCommand.Parameters.Add(new SqlParameter("@MfrDate", oRow["MfrDate"]));
                    oCommand.ExecuteNonQuery();

                }
                return null;
            }
            catch(SqlException ex)
            {
                return ex;
            }
            
        }

        public SqlException UpdateReceipt(ReceiptUnit oUnit)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                string updateSQL = "UPDATE OIGN SET Reference=@Reference, [Trans]=@Trans, Remarks=@Remarks, WhsCode=@WhsCode, WhsName=@WhsName, PostingDate=@PostingDate, DocStatus=@DocStatus, DateModified=@DateModified, ModifiedBy=@ModifiedBy " +
                    "WHERE DocNum=@DocNum";

                oCommand.Connection = this.Connection;
                oCommand.CommandText = updateSQL;
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocNumber));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@Trans", oUnit.Transaction));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@WhsCode", oUnit.WarehouseCode));
                oCommand.Parameters.Add(new SqlParameter("@WhsName", oUnit.WarehouseName));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));

                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));
                oCommand.ExecuteNonQuery();



                string selectSQL = "SELECT * FROM IGN1 WHERE DocNum=@DocNum";


                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = selectSQL;
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocNumber));
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                if (oUnit.DocumentStatus == "Posted")
                {
                    foreach (DataRow oRow in ds.Tables[0].Rows)
                    {
                        StockManager oManager = new StockManager();
                        oManager.Connection = this.Connection;
                        oManager.UpdateStocks(oUnit.WarehouseCode, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), (Convert.ToDouble(oRow["Qty"].ToString()) * -1).ToString());

                    }
                }


                string deleteSQL = "DELETE FROM IGN1 WHERE DocNum=@DocNum";

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = deleteSQL;
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocNumber));
                oCommand.ExecuteNonQuery();


                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {

                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        string insertSQL = "INSERT INTO IGN1 (DocNum, ItemCode, ItemName, Qty, UnitCost, UoM, LotNumber, MfrDate, ExpiryDate) VALUES (@DocNum, @ItemCode, @ItemName, @Qty, @UnitCost, @UoM, @LotNumber,  @MfrDate, @ExpiryDate)";

                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = insertSQL;
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemName", oRow["ItemName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Qty", oRow["Qty"]));
                        oCommand.Parameters.Add(new SqlParameter("@UnitCost", oRow["UnitCost"]));
                        oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", oRow["ExpiryDate"]));
                        oCommand.Parameters.Add(new SqlParameter("@MfrDate", oRow["MfrDate"]));

                        oCommand.ExecuteNonQuery();
                        if (oUnit.DocumentStatus == "Posted")
                        {
                            StockManager oManager = new StockManager();
                            oManager.Connection = this.Connection;

                            if (oManager.IsLotNumberExists(oUnit.WarehouseCode, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString()))
                            {
                                oManager.UpdateStocks(oUnit.WarehouseCode, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), oRow["Qty"].ToString());
                            }
                            else
                            {
                                oManager.AddStocks(oUnit.WarehouseCode, oUnit.WarehouseName, oRow["ItemCode"].ToString(), oRow["ItemName"].ToString(), oRow["Qty"].ToString(), oRow["UoM"].ToString(), oRow["LotNumber"].ToString(), oRow["MfrDate"].ToString(), oRow["ExpiryDate"].ToString());
                            }
                        }

                    }
                }

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
        }

        public void PostReceipt(string _WAREHOUSE, string _DOCNUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OIGN SET DOCSTATUS='Posted' where DocNum=@DocNumber";
            oCommand.Parameters.Add(new SqlParameter("@DocNumber", _DOCNUMBER));
            oCommand.ExecuteNonQuery();


            
            string selectSQL = "SELECT * FROM IGN1 WHERE DocNum=@DocNum";


            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = selectSQL;
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            
            foreach (DataRow oRow in ds.Tables[0].Rows)
            {
                StockManager oManager = new StockManager();
                oManager.Connection = this.Connection;

                if (oManager.IsLotNumberExists(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString()))
                {
                    oManager.UpdateStocks(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), oRow["Qty"].ToString(), oRow["UnitCost"].ToString());
                }
                else
                {
                    WarehouseManager oWhs = new WarehouseManager();
                    oWhs.ConnectionString = this.ConnectionString;
                    oWhs.Open();
                    DataTable dt = oWhs.GetWarehouse(_WAREHOUSE);
                    oWhs.Close();
                    oManager.AddStocks(_WAREHOUSE, dt.Rows[0]["Dscription"].ToString(), oRow["ItemCode"].ToString(), oRow["ItemName"].ToString(), oRow["Qty"].ToString(), oRow["UoM"].ToString(), oRow["LotNumber"].ToString(), oRow["MfrDate"].ToString(), oRow["ExpiryDate"].ToString(), oRow["UnitCost"].ToString(), "N/A", "Manual Receipt");
                }
            }

                        
        }

        public void CancelReceipt(string _DOCNUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OIGN SET DOCSTATUS='Canceled' where DocNum=@DocNumber";
            oCommand.Parameters.Add(new SqlParameter("@DocNumber", _DOCNUMBER));
            oCommand.ExecuteNonQuery();
        }

        #region Get Data

        public DataTable GetTransactionLogPerDocument(DateTime _FROM, DateTime _TO)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_LOGS_IGN";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetTransactionLogPerItem(DateTime _FROM, DateTime _TO)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_LOGS_IGN_PER_ITEM";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }


        public DataTable GetTransaction()
        {

            
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM M_RECEIPT_TRANS";
            

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetReceipts(string sDocumentNumber)
        {

            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, Reference, Remarks, WhsCode, DocStatus FROM OIGN WHERE DocNum LIKE @DOCNUM ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }
        
        public DataTable GetReceiptDocument(string sDocumentNumber)
        {


            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OIGN WHERE DocNum=@DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetReceiptLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM IGN1 WHERE DocNum=@DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        #endregion

        #region Report Retreivable

        public DataTable GetReceiptStatus(DateTime _FROM, DateTime _TO)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_IGN_STATUS";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        #endregion
    }
}
